package cn.com.hhrcw.enterprise.web;

import cn.com.hhrcw.base.Result;
import cn.com.hhrcw.enterprise.params.EntryParam;
import cn.com.hhrcw.enterprise.params.StatisticsInfoParams;
import cn.com.hhrcw.enterprise.service.EnterpriseService;
import cn.com.hhrcw.enterprise.vo.ExcelEntryInfoVO;
import cn.com.hhrcw.enterprise.vo.ExcelJobAnalysisVO;
import cn.com.hhrcw.enterprise.vo.ExcelJobsStatisticsVO;
import cn.com.hhrcw.entity.*;
import cn.com.hhrcw.position.entity.RecruitmentPosition;
import cn.com.hhrcw.position.service.IRecruitmentPositionService;
import cn.com.hhrcw.service.*;
import cn.com.hhrcw.statistics.IStatisticsService;
import cn.com.hhrcw.statistics.entity.Statistics;
import cn.com.hhrcw.utils.DateUtil;
import cn.com.hhrcw.utils.ExcelUtil;
import cn.com.hhrcw.utils.ProvinceCityCountyUtils;
import cn.com.hhrcw.utils.edu.EduLabelUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.core.util.URLUtil;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.additional.query.impl.LambdaQueryChainWrapper;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.SecurityUtils;
import org.jeecg.common.constant.CommonConstant;
import org.jeecg.common.system.vo.LoginUser;
import org.jeecg.common.util.DateUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.util.CollectionUtils;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * excel导出
 *
 * @program: recruit-boot-parent
 * @author: JD
 * @create: 2020-11-14 13:43
 **/
@Controller
@Slf4j
@RequestMapping("/en/enExcel")
@Api(tags = "excel导出")
@Validated
@CrossOrigin
public class EnExcelController {

    @Autowired
    private IOpDeliveryHistoryService deliveryHistoryService;

    @Autowired
    private IOpJobService jobService;

    @Autowired
    private EduLabelUtil eduLabelUtil;

    @Autowired
    private IOpInterviewHistoryService interviewHistoryService;

    @Autowired
    EnterpriseService enterpriseService;

    @Autowired
    ISysUserDepartService userDepartService;

    @Autowired
    private ICvResumeService resumeService;

    @Autowired
    private IEnInfoService enInfoService;

    @Autowired
    private IPiInfoService piInfoService;

    @Autowired
    private ICvUserEduInfoService eduInfoService;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    private ICvUserWorkInfoService workInfoService;

    @Autowired
    private ICvWorkIntentionService workIntentionService;

    @Autowired
    private ICvCertificateService certificateService;

    @Autowired
    private IOpBrowseHistoryService browseHistoryService;

    @Autowired
    private IOpEntryHistoryService entryHistoryService;

    @Autowired
    IRecruitmentPositionService positionService;

    @Autowired
    private IStatisticsService statisticsService;

    @Autowired
    private ICvSkillsService cvSkillsService;

    @Autowired
    private RedisTemplate redisTemplate;

    @PostMapping("/getUserList")
    @ResponseBody
    public Result getUserList(@RequestBody EntryParam params) {
        Map<String, Object> queryMap = new HashMap<>(16);
        LoginUser loginUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        SysUserDepart depart = userDepartService.lambdaQuery().eq(SysUserDepart::getUserId, loginUser.getId()).one();
        queryMap.put("companyId", depart.getDepId());
        if (StrUtil.isNotBlank(params.getDataId())) {
            queryMap.put("deliveryId", params.getDataId().split(","));
        }
        queryMap.put("sysFrom", params.getSysFrom());
        queryMap.put("bsType", params.getBsType());
        queryMap.put("bsId", params.getSsId());
        queryMap.put("state", null);
        int five = 5;
        if (params.getState() != null) {
            if (params.getState() == 0) {
                queryMap.put("state", new Integer[]{0, 1});
            } else if (params.getState() == five) {
                queryMap.put("state", new Integer[]{5, 9});
            } else {
                queryMap.put("state", new Integer[]{params.getState()});
            }
        }
        queryMap.put("jobName", params.getJobName());
        queryMap.put("name", params.getUserName());
        //            应聘时间范围 0 近三个月 1近半年
        queryMap.put("createTimeBegin", null);
        queryMap.put("createTimeEnd", null);
        Date now = new Date();
        if (params.getTimeState() != null && params.getTimeState() == 0) {
            queryMap.put("createTimeBegin", cn.hutool.core.date.DateUtil.offsetMonth(now, -3));
        } else if (params.getTimeState() != null && params.getTimeState() == 1) {
            queryMap.put("createTimeBegin", cn.hutool.core.date.DateUtil.offsetMonth(now, -6));
        }
        queryMap.put("createTimeEnd", now);

        if (params.getMinWorkYear() != null && params.getMaxWorkYear() != null) {
            if (params.getMinWorkYear() == 0 && params.getMaxWorkYear() == 0) {
                queryMap.put("jobTimeBegin", null);
                queryMap.put("jobTimeEnd", null);
            } else {
                String start = DateUtil.getYearFirst(params.getMaxWorkYear());
                String end = DateUtil.getYearLast(params.getMinWorkYear());
                queryMap.put("jobTimeBegin", start);
                queryMap.put("jobTimeEnd", end);
            }
        }
        queryMap.put("queryType", "resumeDeliveryList");
        String uuid = IdUtil.fastSimpleUUID();
        redisTemplate.opsForValue().set(uuid, queryMap, 5, TimeUnit.MINUTES);
        return Result.ok((Object) (uuid));
    }

    /**
     * 导出人才库列表
     *
     * @param params
     * @return cn.com.hhrcw.base.Result
     */
    @PostMapping("/exportTalentList")
    @ResponseBody
    public Result exportTalentList(@RequestBody EntryParam params) {
        LoginUser loginUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        SysUserDepart depart = userDepartService.lambdaQuery().eq(SysUserDepart::getUserId, loginUser.getId()).one();
        Map<String, Object> queryMap = new HashMap<>(16);
        queryMap.put("companyId", depart.getDepId());
        if (StrUtil.isNotBlank(params.getJobName())) {
            List<RecruitmentPosition> positions = positionService.lambdaQuery()
                .select(RecruitmentPosition::getId)
                .like(RecruitmentPosition::getName, params.getJobName())
                .list();
            queryMap.put("positions", positions.stream().map(RecruitmentPosition::getId).toArray());
        }
        if (StrUtil.isNotBlank(params.getUserId())) {
            queryMap.put("userId", params.getUserId().split(","));
        }
        queryMap.put("sysFrom", params.getSysFrom());
        queryMap.put("sex", params.getSex());
        queryMap.put("bsType", params.getBsType());
        queryMap.put("bsId", params.getSsId());
        queryMap.put("maritalStatus", params.getMaritalStatus());
        queryMap.put("state", null);
        int five = 5;
        if (params.getState() != null) {
            if (params.getState() == 0) {
                queryMap.put("state", new Integer[]{0, 1});
            } else if (params.getState() == five) {
                queryMap.put("state", new Integer[]{5, 9});
            } else {
                queryMap.put("state", new Integer[]{params.getState()});
            }
        }
        queryMap.put("name", params.getUserName());
        //            应聘时间范围 0 近三个月 1近半年
        queryMap.put("createTimeBegin", null);
        queryMap.put("createTimeEnd", null);
        Date now = new Date();
        if (params.getTimeState() != null && params.getTimeState() == 0) {
            queryMap.put("createTimeBegin", cn.hutool.core.date.DateUtil.offsetMonth(now, -3));
        } else if (params.getTimeState() != null && params.getTimeState() == 1) {
            queryMap.put("createTimeBegin", cn.hutool.core.date.DateUtil.offsetMonth(now, -6));
        }
        queryMap.put("createTimeEnd", now);
        //工作经验
        if (params.getMinWorkYear() != null && params.getMaxWorkYear() != null) {
            if (params.getMinWorkYear() == 0 && params.getMaxWorkYear() == 0) {
                queryMap.put("jobTimeBegin", null);
                queryMap.put("jobTimeEnd", null);
            } else {
                DateTime start = cn.hutool.core.date.DateUtil.offsetMonth(now, -(params.getMaxWorkYear() * 12));
                DateTime end = cn.hutool.core.date.DateUtil.offsetMonth(now, -(params.getMinWorkYear() * 12));
                queryMap.put("jobTimeBegin", start.toString(DatePattern.NORM_DATE_PATTERN));
                queryMap.put("jobTimeEnd", end.toString(DatePattern.NORM_DATE_PATTERN));
            }
        }
        //期望月薪
        if (params.getMinMoney() != null && params.getMaxMoney() != null) {
            queryMap.put("minMoney", params.getMinMoney());
            queryMap.put("maxMoney", params.getMaxMoney());
        }
        //年龄
        if (params.getMinAge() != null) {
            String endBirthday = DateUtil.getYearFirst(params.getMinAge() - 1);
            queryMap.put("endBirthday", endBirthday);

        }
        if (params.getMaxAge() != null) {
            String beginBirthday = DateUtil.getYearFirst(params.getMaxAge());
            queryMap.put("beginBirthday", beginBirthday);
        }
        queryMap.put("queryType", "resumeTalentList");
        String uuid = IdUtil.fastSimpleUUID();
        redisTemplate.opsForValue().set(uuid, queryMap, 5, TimeUnit.MINUTES);
        return Result.ok((Object) (uuid));
    }

    /**
     * 简历库-人才库-导出名单,支持导出时加查询条件
     *
     * @return
     */
    @GetMapping("/exportData")
    public void exportData(String id, HttpServletResponse response) {
        Assert.notBlank(id, "导出失败, 没有权限");
        Assert.isTrue(redisTemplate.hasKey(id), "导出失败, 没有权限");
        Map queryMap = (Map) redisTemplate.opsForValue().get(id);
        response.setContentType("application/octet-stream");
        // 设置在下载框默认显示的文件名
        String encode = URLUtil.encode("名单.xlsx", StandardCharsets.UTF_8.name());
        response.setHeader("Content-Disposition", "attachment;filename=" + encode);

        try {
            Object queryType = queryMap.get("queryType");
            IPage<OpDeliveryHistory> opDeliveryHistoryPage = null;
            //人才库
            boolean resumeTalentList = StrUtil.equals("resumeTalentList", queryType.toString());
            if (resumeTalentList) {
                opDeliveryHistoryPage = this.deliveryHistoryService.resumeTalentList(new Page(1, -1), queryMap);
            }
            //候选人
            else {
                opDeliveryHistoryPage = this.deliveryHistoryService.resumeDeliveryList(new Page(1, -1), queryMap);
            }
            List<OpDeliveryHistory> records = opDeliveryHistoryPage.getRecords();
            if (CollUtil.isEmpty(records)) {
                return;
            }
            List<OpDeliveryHistory> histories = resumeTalentList ? null : records;
            ServletOutputStream out = response.getOutputStream();
            EasyExcel.write(out, ExcelEntryInfoVO.class).sheet().doWrite(() -> {
                List<ExcelEntryInfoVO> voList = new ArrayList<>();
                Object[] array = records.stream().map(OpDeliveryHistory::getUserId).distinct().toArray();
                List<PiInfo> list = this.piInfoService.lambdaQuery()
                    .in(PiInfo::getUserId, array)
                    .list();
                this.data(list, voList, histories);
                return voList;
            });
            out.flush();
        } catch (Exception e) {
            response.reset();
            log.error(e.getMessage(), e);
        }
    }

    public void data(List<PiInfo> piInfos, List<ExcelEntryInfoVO> receive, List<OpDeliveryHistory> deliveryHistories) {
        piInfos.parallelStream().forEach(e -> {
            ExcelEntryInfoVO vo = new ExcelEntryInfoVO();

            String userId = e.getUserId();
            CvResume resume = resumeService.lambdaQuery()
                .eq(CvResume::getUserId, userId)
                .eq(CvResume::getDelFlag, false)
                .eq(CvResume::getType, 0)
                .eq(CvResume::getLanType, 0)
                .one();
            if (resume != null) {
                String resumeType = resume.getResumeType() == 1 ? "社招" : "校招";
                vo.setResumeType(resumeType);
                vo.setOffice(resume.getOtherSituations());
            }
            List<OpDeliveryHistory> deliveryHistoryList = deliveryHistories;
            if (deliveryHistoryList == null) {
                deliveryHistoryList = deliveryHistoryService.lambdaQuery()
                    .eq(OpDeliveryHistory::getUserId, userId)
                    .eq(OpDeliveryHistory::getDelFlag, 0)
                    .orderByDesc(OpDeliveryHistory::getCreateTime).list();
            } else {
                deliveryHistoryList = deliveryHistories.stream().filter(el -> StrUtil.equals(el.getUserId(), userId)).collect(Collectors.toList());
            }
            // 根据最新的投递记录查询出投递职位信息
            if (CollUtil.isNotEmpty(deliveryHistoryList)) {
                List<String> position = new ArrayList<>();
                List<String> companyName = new ArrayList<>();
                for (OpDeliveryHistory history : deliveryHistoryList) {
                    String jobId = history.getJobId();
                    OpJob byId = jobService.getById(jobId);
                    if (null != byId) {
                        position.add(byId.getJobName());
                        EnInfo company = this.enInfoService.getById(byId.getCompanyId());
                        companyName.add(company.getEnName());
                    }
                }
                vo.setPosition(CollUtil.join(position, "\n"));
                vo.setCompanyName(CollUtil.join(companyName, "\n"));
            }
            vo.setRate(new BigDecimal("0.00"));
            if (StringUtils.isNotBlank(e.getLiveCity())) {
                String[] citys = e.getLiveCity().split(",");
                String liveCity = getTransNames("sys_area", citys, "id", "name");
                vo.setLiveCity(liveCity);
            }

            if (StringUtils.isNotBlank(e.getHomeTown())) {
                String[] citys = e.getHomeTown().split(",");
                String liveCity = getTransNames("sys_area", citys, "id", "name");
                vo.setNativePlace(liveCity);
            }
            int two = 2;
            int three = 3;
            if (e.getMaritalStatus() != null) {
                if (0 == e.getMaritalStatus()) {
                    vo.setMaritalStatus("未婚");
                } else if (1 == e.getMaritalStatus()) {
                    vo.setMaritalStatus("已婚已育");
                } else if (two == e.getMaritalStatus()) {
                    vo.setMaritalStatus("已婚未育");
                } else if (three == e.getMaritalStatus()) {
                    vo.setMaritalStatus("离异");
                }

            }

            Date birthday = e.getBirthday();
            if (birthday != null) {
                Integer age = e.getAge();
                vo.setAge(cn.hutool.core.date.DateUtil.format(birthday, "yyyy年MM月") + (age != null ? "(" + age + "岁)" : ""));
            }
            vo.setEmail(e.getEmail());
            vo.setName(e.getName());
            int d = 2;
            if (e.getSex() == null || e.getSex() == 0) {
                vo.setSex("未知");
            } else if (e.getSex() != null && e.getSex() == 1) {
                vo.setSex("男");
            } else if (e.getSex() != null && e.getSex() == d) {
                vo.setSex("女");
            }
            vo.setPhone(e.getPhone());
            vo.setPolitics(e.getPolitical());

            LambdaQueryWrapper<CvUserEduInfo> eduQuery = new LambdaQueryWrapper<>();
            eduQuery.eq(CvUserEduInfo::getDelFlag, false).eq(CvUserEduInfo::getUserId, e.getUserId()).orderByDesc(CvUserEduInfo::getStartEduTime);
            //                        .orderByDesc(CvUserEduInfo::getEduLevel);
            //教育经历
            List<CvUserEduInfo> eduInfos = eduInfoService.list(eduQuery);
            if (!CollectionUtils.isEmpty(eduInfos)) {

                StringBuffer education = new StringBuffer();
                for (CvUserEduInfo eduInfo : eduInfos) {
                    //就读时间  学校名称    专业  学历
                    String info = StrUtil.format("{}-{}   {}   {}   {}  {}",
                        cn.hutool.core.date.DateUtil.format(eduInfo.getStartEduTime(), "yyyy.MM"),
                        cn.hutool.core.date.DateUtil.format(eduInfo.getEndEduTime(), "yyyy.MM"),
                        eduInfo.getSchoolName(), eduInfo.getMajor(), eduLabelUtil.getEduLevel(eduInfo.getEduLevel())
                        , "1".equalsIgnoreCase(eduInfo.getR2()) ? "全日制" : "非全日制"//

                    );
                    education.append(info).append("\n");
                }
                vo.setEducation(education.toString().trim());

                vo.setFullTimeLable("1".equalsIgnoreCase(eduInfos.get(0).getR2()) ? "是" : "否");
            }

            LambdaQueryWrapper<CvUserWorkInfo> workInfoLambdaQueryWrapper = new LambdaQueryWrapper<>();
            workInfoLambdaQueryWrapper.eq(CvUserWorkInfo::getDelFlag, false).eq(CvUserWorkInfo::getUserId, e.getUserId()).orderByDesc(CvUserWorkInfo::getCreateTime);
            //工作经历
            List<CvUserWorkInfo> workInfos = workInfoService.list(workInfoLambdaQueryWrapper);
            if (!CollectionUtils.isEmpty(workInfos)) {
                StringBuffer work = new StringBuffer();
                for (CvUserWorkInfo workInfo : workInfos) {
                    if (StrUtil.isNotBlank(workInfo.getPositionName())) {
                        String[] array = workInfo.getPositionName().split(",");
                        String jobName = getTransName("recruitment_position", array[array.length - 1], "id", "name");
                        String info = StrUtil.format("{}-{}   {}   {}",
                            cn.hutool.core.date.DateUtil.format(workInfo.getWorkStartTime(), "yyyy.MM"),
//                            cn.hutool.core.date.DateUtil.format(workInfo.getWorkEndTime(), "yyyy.MM"),
                            workInfo.getWorkEndTime().equals(CommonConstant.TO_THIS_DAY)? workInfo.getWorkEndTime():cn.hutool.core.date.DateUtil.format(DateUtils.str2Date(workInfo.getWorkEndTime(), DateUtils.date_sdf.get()), "yyyy.MM"),
                            workInfo.getCompanyName(), jobName
                        );
                        work.append(info).append("\n");
                    }
                }
                vo.setWork(work.toString().trim());
            }

            LambdaQueryWrapper<CvWorkIntention> workIntentionLambdaQueryWrapper = new LambdaQueryWrapper<>();
            workIntentionLambdaQueryWrapper.eq(CvWorkIntention::getDelFlag, false).eq(CvWorkIntention::getUserId, e.getUserId()).orderByDesc(CvWorkIntention::getCreateTime);
            List<CvWorkIntention> workIntentions = workIntentionService.list(workIntentionLambdaQueryWrapper);
            if (!CollectionUtils.isEmpty(workIntentions)) {
                CvWorkIntention workIntention = workIntentions.get(0);
                vo.setMoney(workIntention.getMinMoney() + "-" + workIntention.getMaxMoney());
                if (StrUtil.isNotBlank(workIntention.getWorkIndustry())) {
                    String[] array = workIntention.getWorkIndustry().split(",");
                    vo.setHopeIndustry(getTransName("sys_industry", array[array.length - 1], "id", "name"));
                }
            }

            List<CvCertificate> cvCertificates = certificateService.lambdaQuery().eq(CvCertificate::getDelFlag, false).eq(CvCertificate::getUserId, e.getUserId()).list();
            if (!CollectionUtils.isEmpty(cvCertificates)) {
                cvCertificates.forEach(c -> {
                    if (StringUtils.isNotBlank(vo.getCertificates())) {
                        vo.setCertificates(vo.getCertificates() + " " + c.getName());
                    } else {
                        vo.setCertificates(c.getName());
                    }

                });

            }

            Integer browseCount = browseHistoryService.lambdaQuery().eq(OpBrowseHistory::getDelFlag, false).eq(OpBrowseHistory::getUserId, e.getUserId()).count();
            vo.setCount(browseCount == null ? "0" : String.valueOf(browseCount));
            if (browseCount != null && browseCount > 0) {
                Integer count = deliveryHistoryService.lambdaQuery().eq(OpDeliveryHistory::getDelFlag, false).eq(OpDeliveryHistory::getUserId, e.getUserId()).count();
                if (count != null && count > 0) {
                    vo.setRate(new BigDecimal(count).divide(new BigDecimal(browseCount), 2));
                } else {
                    vo.setRate(BigDecimal.ZERO);
                }
            } else {
                vo.setRate(BigDecimal.ZERO);
            }

            //2022年4月1日11:02:30 zhf  职业特长与技能（专业技能）--->改为了职称
            LambdaQueryWrapper<CvSkills> skillsQuery = new LambdaQueryWrapper<>();
            skillsQuery.eq(CvSkills::getDelFlag, false).eq(CvSkills::getResumeId, resume.getId())
                .orderByDesc(CvSkills::getId);

            StringBuilder skillsInfosStr = new StringBuilder();
            List<CvSkills> cvSkillsInfos = cvSkillsService.list(skillsQuery);
            if (CollUtil.isNotEmpty(cvSkillsInfos)) {
                cvSkillsInfos.forEach(e2 -> {
                    skillsInfosStr.append(e2.getName()).append("\r\n");
                });
            }
            vo.setSkill(skillsInfosStr.toString().trim());
            receive.add(vo);
        });
    }

    public String getTransNames(String table, String[] code, String column, String columnValue) {
        try {
            StringBuilder sql = new StringBuilder("select " + columnValue + " from `" + table + "` where " + column + " in (");
            for (String c : code) {
                sql.append("'" + c + "',");
            }
            sql.deleteCharAt(sql.length() - 1);
            sql.append(")");
            List<String> value = jdbcTemplate.queryForList(sql.toString(), String.class);
            //StringBuilder res = new StringBuilder();
            //if (!CollectionUtils.isEmpty(value)) {
            //    value.forEach(e -> {
            //        res.append(e + ",");
            //    });
            //    res.deleteCharAt(res.length() - 1);
            //}
            return ProvinceCityCountyUtils.getProvinceCityCountyShort(value);
        } catch (Exception e) {
            return "";
        }
    }

    public String getTransName(String table, String code, String column, String columnValue) {
        try {
            String value = jdbcTemplate.queryForObject("select " + columnValue
                + " from `" + table + "` where " + column + " = ?", new Object[]{code}, String.class);
            return value;
        } catch (Exception e) {
            return "";
        }
    }

    /**
     * 招聘报表-招聘进度及发布岗位-导出下面的列表
     *
     * @param params
     * @return
     */
    @GetMapping("/jobInfos")
    public void getStatisticsInfo(HttpServletResponse response, StatisticsInfoParams params) {
        LoginUser loginUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        LambdaQueryChainWrapper<OpJob> wrapper = this.jobService.lambdaQuery();
        wrapper.eq(OpJob::getCompanyId, loginUser.getCompanyId());
        wrapper.eq(OpJob::getDelFlag, false);
        if (StringUtils.isNotBlank(params.getJobName())) {
            wrapper.like(OpJob::getJobName, params.getJobName());
        }
        if (params.getStartTime() != null) {
            wrapper.ge(OpJob::getCreateTime, params.getStartTime());
        }
        if (params.getEndTime() != null) {
            wrapper.le(OpJob::getCreateTime, cn.hutool.core.date.DateUtil.endOfDay(cn.hutool.core.date.DateUtil.parse(params.getEndTime())));
        }
        if (StringUtils.isNotBlank(params.getJobId())) {
            wrapper.in(OpJob::getId, params.getJobId().split(","));
        }
        List<OpJob> list = wrapper.list();
        List<ExcelJobsStatisticsVO> resList = new ArrayList<>();
        if (!CollectionUtils.isEmpty(list)) {
            list.forEach(opJob -> {
                ExcelJobsStatisticsVO res = new ExcelJobsStatisticsVO();
                res.setJobId(opJob.getId());
                res.setJobName(opJob.getJobName());
                res.setJobTime(opJob.getCreateTime());
                res.setPlaneCount(opJob.getNeedCount());
                res.setCompletionRate(new BigDecimal("0.00"));
                if (opJob.getDelFlag()) {
                    res.setState(2);
                    res.setStateString("已删除");
                } else {
                    if (opJob.getRecruitmentState() == 0) {
                        res.setState(0);
                        res.setStateString("在招");
                    } else {
                        res.setState(1);
                        res.setStateString("已下架");
                    }
                }
                EnInfo enInfo = enInfoService.getById(opJob.getCompanyId());
                if (enInfo == null) {
                    enInfo = new EnInfo();
                }
                res.setComName(enInfo.getEnName());
                res.setJobId(opJob.getId());

                Statistics statistics = statisticsService.queryByJob(opJob.getId());
                res.setPlaneCount(statistics.getRecruitment());

                res.setDeCount(statistics.getTotal());
                res.setScreenCount(statistics.getTotal() - statistics.getFilterBySystem());
                res.setFirstScrCount(statistics.getFilterFirst());

                res.setViewCount(statistics.getAlreadyInterview());
                res.setEntryCount(statistics.getAccession());
                res.setNotCount(statistics.getRemaining());
                res.setOfferCount(statistics.getOffer());
                res.setCompletionRate(NumberUtil.round(NumberUtil.div(res.getEntryCount(), opJob.getNeedCount()), 2));
                resList.add(res);
            });
            ExcelUtil<ExcelJobsStatisticsVO> excelUtil = new ExcelUtil<>();
            try {
                excelUtil.buildExcel(response, resList);
            } catch (
                IOException e) {
                e.printStackTrace();
            } catch (
                IllegalAccessException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 招聘报表-招聘环节分析-各个职位信息统计
     *
     * @param params
     * @return
     */
    @GetMapping("/jobAnalysisInfo")
    public void jobAnalysisInfo(HttpServletResponse response, StatisticsInfoParams params) throws IOException, IllegalAccessException {
        LambdaQueryWrapper<OpJob> jobQuery = new LambdaQueryWrapper<>();
        jobQuery.eq(OpJob::getRecruitmentState, 0);
        if (StringUtils.isNotBlank(params.getJobName())) {
            jobQuery.like(OpJob::getJobName, params.getJobName());
        }
        if (params.getStartTime() != null) {
            jobQuery.ge(OpJob::getCreateTime, params.getStartTime());
        }
        if (params.getEndTime() != null) {
            jobQuery.le(OpJob::getCreateTime, cn.hutool.core.date.DateUtil.endOfDay(cn.hutool.core.date.DateUtil.parse(params.getEndTime())));
        }
        if (StringUtils.isNotBlank(params.getJobId())) {
            String id = params.getJobId(), contStr = ",";
            if (id.contains(contStr)) {
                jobQuery.in(OpJob::getId, id.split(contStr));
            } else {
                jobQuery.in(OpJob::getId, id);
            }
        }
        LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        jobQuery.eq(OpJob::getCreateBy, sysUser.getId());
        List<OpJob> list = jobService.list(jobQuery);
        List<ExcelJobAnalysisVO> result = jobAnalysisInfoList(list);
        ExcelUtil<ExcelJobAnalysisVO> excelUtil = new ExcelUtil<>();
        excelUtil.buildExcel(response, result);
    }

    private List<ExcelJobAnalysisVO> jobAnalysisInfoList(List<OpJob> list) {
        List<ExcelJobAnalysisVO> result = new ArrayList<>();
        if (!CollectionUtils.isEmpty(list)) {
            list.forEach(job -> {
                ExcelJobAnalysisVO vo = new ExcelJobAnalysisVO();
                EnInfo enInfo = enInfoService.getById(job.getCompanyId());
                vo.setComId(enInfo.getEnName());
                vo.setComId(job.getCompanyId());
                vo.setJobId(job.getId());
                vo.setJobName(job.getJobName());
                vo.setJobTime(job.getCreateTime());
                if (job.getDelFlag()) {
                    vo.setState(2);
                    vo.setStateString("已删除");
                } else {
                    if (job.getRecruitmentState() == 0) {
                        vo.setState(0);
                        vo.setStateString("在招");
                    } else {
                        vo.setState(1);
                        vo.setStateString("已下架");
                    }
                }
                //查找此职位的投递记录
                LambdaQueryWrapper<OpDeliveryHistory> deliveryQuery = new LambdaQueryWrapper<>();
                deliveryQuery.eq(OpDeliveryHistory::getJobId, job.getId()).eq(OpDeliveryHistory::getScreenFlag, true);
                List<OpDeliveryHistory> deliveryHistories = deliveryHistoryService.list(deliveryQuery);
                if (!CollectionUtils.isEmpty(deliveryHistories)) {
                    //0 未阅读  1 已阅读 2 不合适 3通过筛选4 面试 5已发offer(已录用) 6 已入职 7 被系统过滤 8未入职9反馈面试结果(通过)10反馈面试结果(不通过)
                    //初筛数量
                    long firstScrCount = deliveryHistories.stream().filter(history -> history.getState() >= 3).count();
                    vo.setFirstScrCount((int) firstScrCount);
                    //初筛通过率
                    if (deliveryHistories.size() > 0) {
                        vo.setFirstScrRate(NumberUtil.round(NumberUtil.div(firstScrCount, deliveryHistories.size()), 2));
                    }
                    //面试
                    List<OpInterviewHistory> interviewHistories = interviewHistoryService.lambdaQuery().eq(OpInterviewHistory::getJobId, job.getId()).list();
                    vo.setViewCount(CollectionUtils.isEmpty(interviewHistories) ? 0 : interviewHistories.size());
                    //邀约面试率   面试邀请数量/通过初筛数量
                    if (firstScrCount > 0) {
                        vo.setViewRate(NumberUtil.round(NumberUtil.div(interviewHistories.size(), firstScrCount), 2));
                    }
                    //已面试数量
                    long alreadyViewCount = interviewHistories.stream().filter(history -> history.getOutLineState() == 1 && history.getViewResult() != null).count();
                    vo.setAlreadyViewCount((int) alreadyViewCount);
                    //接收面试数量
                    long accept = interviewHistories.stream().filter(history -> history.getOutLineState() == 1).count();
                    //面试到场率   已面试数量/接收面试数量
                    if (accept > 0) {
                        vo.setPresentRate(NumberUtil.round(NumberUtil.div(alreadyViewCount, accept), 2));
                    }
                    //通过面试数量
                    long adoptCount = interviewHistories.stream().filter(history -> history.getViewResult() != null && history.getViewResult() == 0).count();
                    vo.setAdoptCount((int) adoptCount);
                    //面试通过率  通过面试数量/已面试数量
                    if (alreadyViewCount > 0) {
                        vo.setAdoptRate(NumberUtil.round(NumberUtil.div(adoptCount, alreadyViewCount), 2));
                    }
                    //录用
                    List<OpEntryHistory> entryHistories = this.entryHistoryService.lambdaQuery().eq(OpEntryHistory::getJobId, job.getId()).list();
                    vo.setEmployCount(entryHistories.size());
                    //录用率  入职邀请数量/面试总数量
                    if (interviewHistories.size() > 0) {
                        vo.setEmployRate(NumberUtil.round(NumberUtil.div(entryHistories.size(), interviewHistories.size()), 2));
                    }
                    long entryCount = deliveryHistories.stream().filter(history -> history.getState() == 6).count();
                    vo.setEntryCount((int) entryCount);
                    long entryAccept = entryHistories.stream().filter(history -> history.getAcceptFlag() == 1).count();
                    //到岗率   已入职数量/接受入职邀请数量
                    if (entryAccept > 0) {
                        vo.setPositionRate(NumberUtil.round(NumberUtil.div(entryCount, entryAccept), 2));
                    }
                    long lossCount = entryHistories.stream().filter(history -> history.getAcceptFlag() == 2).count();
                    vo.setLossCount((int) lossCount);
                    //流失率   未接受入职邀请数量/总入职邀请数量
                    if (entryHistories.size() > 0) {
                        vo.setLossRate(NumberUtil.round(NumberUtil.div(lossCount, entryHistories.size()), 2));
                    }
                }
                result.add(vo);
            });
        }
        return result;
    }
}
